package com.ruoyi.util.mysqljdbc;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ruoyi.util.mysqljdbc.Mysql8jdbc.EditOrselect;

/**
 * @Description:四叶草专用版java生成 sql脚本 
 * @ClassName: ExportSqlSyc
 * @author syc
 * @date: 2022年9月16日 上午10:51:06
 */
public class ExportSqlSyc {
	
//	public static void main(String[] args) {		
//		String sqlfilePath = "D:/sqltest/";
//		String tablename = "sys_role";
//		String database = "ruoyi-05-24";
//		String host = "127.0.0.1";
//		String port = "3306";
//		String account = "root";
//		String password = "19901110";
//		ExportSqlSyc exportSqlSyc = new ExportSqlSyc(); 
//		//exportSqlSyc.generateTableSql(sqlfilePath,tablename,dataBase,host,port,account,password);
//		String sql = "show tables";
//		List<String> tableList =  ExportSqlSyc.generateDataBasesSql(Mysql8jdbc.driverMysql8, host, port, database, account, password);
//        if (tableList != null && tableList.size() > 0) {
//        	for (String table : tableList) {			
//        		exportSqlSyc.generateTableSql(sqlfilePath, table, database, host, port,  account,  password);
//			}
//        }
//	}
	
	
	public static List<String> generateDataBasesSql(String driver, String host, String port,String database, String account, String password) {
		/**
		 *	获取数据库下的所有表 
		 */
		List<String> tableList = new ArrayList<String>();
		String sql = "show tables";
		JSONObject json = Mysql8jdbc.jdbcGetJSON(driver, host, port, database, account, password, sql, EditOrselect.select);
		if (json != null && json.getJSONArray(EditOrselect.select.name()) != null && json.getJSONArray(EditOrselect.select.name()).size() > 0) {
			JSONArray jsonArray = json.getJSONArray(EditOrselect.select.name());
			Set<String> keySet = jsonArray.getJSONObject(0).keySet();
			String publickey = "";
			for (String key : keySet) {
				publickey = key;
			}
			for (int i = 0; i < jsonArray.size(); i++) {
			    String value = 	jsonArray.getJSONObject(i).getString(publickey);
				if (value != null && !value.equals("")) {
					tableList.add(value);
				}
			}
		}
		return tableList;
	}  
		
	/***
	 * @Description: 导出表 
	 * @author syc
	 * @date: 2022年9月16日 上午10:54:35
	 */
	public void generateTableSql(String sqlfilePath, String tablename,String dataBase,String host, String port, String account, String password) {	
		/**
		 *	1存储地址
		 */
		String filePath = sqlfilePath + getTimePath();
		List<String> listSql = new ArrayList<String>();
		listSql.addAll(sqlCommentLine(tablename, 1));
		/**
		 *	2生成建表语句
		 */
		String sql = "show create table `" + dataBase + "`.`" + tablename + "`";
		String createTableSql = getCreateTableSql(Mysql8jdbc.driverMysql8, host, port, dataBase, account, password, sql);
		listSql.add(createTableSql);
		listSql.addAll(sqlCommentLine(tablename, 0));
		createFile(listSql, filePath, tablename);
		/**
		 * 3 获取表中数据的条数
		 */
		sql = "SELECT COUNT(1) FROM " +tablename + " ";
		JSONObject json = Mysql8jdbc.jdbcGetJSON(Mysql8jdbc.driverMysql8, host, port, dataBase, account, password, sql, EditOrselect.count);
		Integer rowCount = json.getInteger(EditOrselect.count.name());
		if (rowCount != null && rowCount > 0) {
			int pageSize = 1000;
		    int pageCount = 1;
			if(rowCount > pageSize) {
				pageCount = rowCount / pageSize;
				pageCount = pageCount + ((rowCount % pageSize) > 0 ? 1 : 0);
		    }
			for (int i = 0; i < pageCount ; i++) {
				String selectSql = "SELECT * FROM `" + dataBase + "`.`" + tablename + "` WHERE 1=1 LIMIT " + i * pageSize + "," + pageSize ;	
			    List<String> list =	Mysql8jdbc.jdbcCreactSql(Mysql8jdbc.driverMysql8, host, port, dataBase, account, password, selectSql, tablename);
			    createFile(list, filePath, tablename);
			}
		}
		
	}
	
	/**
	 * @Description: SQL脚本注释分隔 
	 * @author syc
	 * @date: 2022年9月16日 下午4:00:39
	 */
	public List<String> sqlCommentLine(String tablename, int createTableOrdata) {
		List<String> list = new ArrayList<String>();
		list.add("\r");
		list.add("-- ----------------------------\r");
		if (createTableOrdata == 1) {
			list.add("-- Table structure for " + tablename + "\r");
			list.add("-- ----------------------------\r");
			list.add("DROP TABLE IF EXISTS `" + tablename + "`;\r");
		} else {
			list.add("-- Records of " + tablename + "\r");
			list.add("-- ----------------------------\r");
		}
		return list;
 	}
	
	/**
	 * @Description: 创建insertsql.sql文件,并写入数据
	 * @author syc
	 * @date: 2022年9月10日 下午7:30:35
	 */
	private boolean createFile(List<String> sqlList, String filePath, String fileName) {
        if ( StringUtils.isBlank(filePath) || sqlList == null || sqlList.size() == 0 || StringUtils.isBlank(fileName)) {
        	return false;
        }
    	File file = new File(filePath);
        if (!file.exists()) {	
    		if (!file.exists()) {
    			file.mkdirs();//创建多层级目录
    		}
        }
        file = new File(filePath + "/" + "fileName" + ".sql");
        if (!file.exists()) {
        	try {
            	file.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        FileWriter fw = null;
        BufferedWriter bw = null;
        try {
            fw = new FileWriter(file, true);
            bw = new BufferedWriter(fw);
            for (int i = 0; i < sqlList.size(); i++) {
				bw.append(sqlList.get(i));
				bw.append("\n");
			}
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bw.close();
                fw.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return true;
    }
	
	/**
	 * @Description:获取mysql数据库表的-->建表语句 
	 * @author syc
	 * @date: 2022年9月10日 下午5:28:03
	 */
	public String getCreateTableSql(String DriverName, String local, String port, String database, String dbAccount,
			String dbpassword, String sql ) {
		JSONObject json = Mysql8jdbc.jdbcGetJSON(DriverName, local, port, database, dbAccount, dbpassword, sql, EditOrselect.select);
		if (json.getJSONArray(EditOrselect.select.name()) != null && json.getJSONArray(EditOrselect.select.name()).size() > 0) {
			return json.getJSONArray(EditOrselect.select.name()).getJSONObject(0).getString("Create Table");
		}
		return null;
	}
	
	/**
	 * @Description: 
	 * @author syc
	 * @date: 2022年9月10日 下午8:13:44
	 */
	public static String getTimePath() {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
		return sdf.format(new Date());
	}
		
}
